# -*- coding:utf-8 -*-

__author__ = 'dong'


import sys
import shutil
from datetime import datetime
import sqlite3
import csv, codecs, cStringIO


class UnicodeWriter:
    """
    A CSV writer which will write rows to CSV file "f",
    which is encoded in the given encoding.
    """
    def __init__(self, f, dialect=csv.excel, encoding="gbk", **kwds):
        # Redirect output to a queue
        self.queue = cStringIO.StringIO()
        self.writer = csv.writer(self.queue, dialect=dialect, **kwds)
        self.stream = f
        self.encoder = codecs.getincrementalencoder(encoding)()

    def writerow(self, row):
        self.writer.writerow([unicode(s).encode("utf-8") for s in row])
        # Fetch UTF-8 output from the queue ...
        data = self.queue.getvalue()
        data = data.decode("utf-8")
        # ... and reencode it into the target encoding
        data = self.encoder.encode(data)
        # write to the target stream
        self.stream.write(data)
        # empty queue
        self.queue.truncate(0)

    def writerows(self, rows):
        for row in rows:
            self.writerow(row)


def create_csv(db_name):
    conn = sqlite3.connect(db_name)
    # conn = sqlite3.connect('xf_old.db')
    c = conn.cursor()
    c.execute('select * from user')
    writer = UnicodeWriter(open("user.csv", "wb"))
    writer.writerows(c)

    c.execute('select * from article')
    writer = UnicodeWriter(open("article.csv", "wb"))
    writer.writerows(c)

    c.execute('select * from classifymodel')
    writer = UnicodeWriter(open("classifymodel.csv", "w"))
    writer.writerows(c)

    c.execute('select * from loginuser')
    writer = UnicodeWriter(open("loginuser.csv", "w"))
    writer.writerows(c)


class DBI(object):
    """database interface"""
    def __init__(self, conn):
        """keep connection"""
        self._conn = conn

    def store(self, sql, data):
        """store data into database with given sql"""
        curr = self._conn.cursor()
        curr.executemany(sql, data)
        self._conn.commit()
        curr.close()

    def execute(self, sql, *args, **kwgs):
        """execute sql on database"""
        curr = self._conn.cursor()
        for a in args:
            if a:
                curr.execute("insert into classifymodel values (?, ?)", (a[0], a[1].decode('gbk')))
                self._conn.commit()
        curr.close()

    def article_execute(self, sql, *args, **kwgs):
        """execute sql on database"""
        curr = self._conn.cursor()

        n = []
        for a in args:
            if a:
                for ii in a:
                    if ii == 'None':
                        ii = '0'
                    n.append(ii.decode('gbk'))

        a = []
        for i in range(0, 43-len(n)):
            a.append(0)

        curr.execute(sql, tuple(n)+tuple(a), **kwgs)
        self._conn.commit()
        curr.close()


def ipager(serial, pagesize):
    """make serial page by page"""
    buff = []
    for row in serial:
        buff.append(row)
        if len(buff) >= pagesize:
            send, buff, = buff, []
            yield send
    if len(buff):
        yield buff



def import_user():
    '''导入用户'''
    sql="insert into user values (?, ?, ?)"
    with open('user.csv', 'rb') as handle:
        for rows in ipager(csv.reader(handle), 512):
            dbi.store(sql, rows)

def import_article():
    '''导入文章'''
    sql="insert into article values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
    with open('article.csv', 'rb') as handle:
        for rows in ipager(csv.reader(handle), 512):
            # dbi.store(sql, rows)
            for r in rows:
                dbi.article_execute(sql, r)

def import_classifymodel():
    '''导入个性分类'''
    sql="insert into classifymodel values (?, ?)"
    with open('classifymodel.csv', 'rb') as handle:
        for rows in ipager(csv.reader(handle), 512):
            for r in rows:
                dbi.execute(sql, r)

def import_loginuser():
    '''导入登陆用户'''
    sql="insert into loginuser values (?, ?)"
    with open('loginuser.csv', 'rb') as handle:
        for rows in ipager(csv.reader(handle), 512):
            dbi.store(sql, rows)


if __name__ == '__main__':

    # 导出csv数据
    create_csv(sys.argv[1])

    # 复制新的数据库
    new_db_name = '{0}.db'.format(datetime.now().strftime("%Y%m%d%H%M%S"))
    shutil.copy('xf_empty.db', new_db_name)

    dbi = DBI(sqlite3.connect(new_db_name))
    dbi.text_factory = str
    # 写入数据库
    import_article()
    import_classifymodel()
    import_loginuser()
    import_user()

    print u'请把{0}复制xf程序目录并改名为xf.db'.format(new_db_name)
